﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Consulta.Core;
using Softpro.AccesoDatosDB2;

namespace Consulta.Manager
{
    public class MInventario
    {
        public DataTable buscarProductos(string pFiltro, bool pConExi, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if (pFiltro != null && pFiltro.Length > 0)
                {
                    tSql = "SELECT " +
						"T1.ArtProduct As Codigo," + 
						"T1.ArtNombre AS Nombre," + 
						"T1.UniCod AS Unidad," +
						"Sum(T3.Cant) As Existencias," +
						"T1.ArtEstado AS Estado," + 
						"T1.GruCod AS CodGru ," + 
						//"T2.GruDes AS Grupo," +
						//"T1.ArtFuncion As Info, " + 
                        "T1.ArtRef As Ref " +
						"FROM " + 
						"SISCOCRPRO.ARTICULO As T1 " +
                        //"INNER JOIN SISCOCRPRO.COGRUPO As T2 ON T1.GruCod = T2.GruCod " +
						"LEFT OUTER JOIN ALMACEN.SALDOS As T3 ON T3.Producto = T1.ArtProduct ";
					if (pConExi)
                    {
						tSql += " INNER JOIN ALMACEN.ALMACENES As T4 ON T4.Cia = T3.Cia And T4.Almacen= T3.Almacen" + 
								" WHERE T3.Ano = substr(T4.Ejercicio, 1, 2) AND T3.Periodo = substr(T4.Ejercicio, 3, 2)";
					}
                    else
                    {
						tSql += " INNER JOIN ALMACEN.ALMACENES As T4 ON ((T4.Cia = T3.Cia And T4.Almacen= T3.Almacen) OR T3.CIA Is Null)" + 
								" WHERE ((T3.Ano = substr(T4.Ejercicio, 1, 2)" +
								" AND T3.Periodo = substr(T4.Ejercicio, 3, 2)) OR T3.Periodo Is Null)";
                    }
                    try
                    {
                        long tCodigo = Convert.ToInt64(pFiltro);
                        tSql += " AND (T1.ArtProduct = " + pFiltro + ")";
                    }
                    catch (FormatException)
                    {
                        //Si no es numerico busca por el nombre de producto
                        tSql += " AND T1.ArtNombre Like '%" + pFiltro.ToUpper() + "%' ";
                    }
                    tSql += " GROUP BY  T1.ArtProduct, T1.ArtNombre, T1.UniCod, T1.ArtEstado,T1.GruCod, T1.ArtRef ";
                    if (pClausulaOrderBy.Length > 0)
                    {
                        tSql += " ORDER BY " + pClausulaOrderBy;
                    }
                    else
                    {
                        tSql += " ORDER BY T1.ArtProduct";
                    }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public void leerInfoProducto(string pCodigo, out string pNombre, out string pUnidad)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                tSql = "SELECT ArtNombre,UniCod " +
                       "FROM SISCOCRPRO.ARTICULO " +
                       "WHERE ArtProduct = " + pCodigo;
                tabla = p.leerDatosSQL(tSql);
                //Verificar si trae datos
                if (tabla.Rows.Count > 0)
                {
                    DataRow tFila = tabla.Rows[0];
                    pNombre = tFila["ArtNombre"].ToString();
                    pUnidad = tFila["UniCod"].ToString();
                }
                else
                {
                    pNombre = "";
                    pUnidad = "";
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public DataTable leerSaldosAlmacen(string pCodigo, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if (pCodigo != null && pCodigo.Length > 0)
                {
                    tSql = "SELECT '" + pCodigo + "' Articulo, T1.Cia, T1.Almacen CodAlm, Nom_Alma as Nombre, " +
                        " ACant as SaldoInicial, ECant As Entradas, SCant As Salidas , Cant as SaldoActual, Ejercicio as Cierre" +
                        " FROM ALMACEN.Almacenes As T1 " +
                        " INNER JOIN ALMACEN.Saldos As T2 ON (T1.Cia = T2.Cia And T1.Almacen= T2.Almacen) " +
                        " WHERE T2.Ano = substr(T1.Ejercicio, 1, 2)" +
                        " AND T2.Periodo = substr(T1.Ejercicio, 3, 2)" +
                        " AND T2.Producto = '" + pCodigo.ToUpper() + "'";
                    if (pClausulaOrderBy.Length > 0)
                    {
                        tSql += " ORDER BY " + pClausulaOrderBy;
                    }
                    else
                    {
                        tSql += " ORDER BY T1.Cia";
                    }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public DataTable leerMovimientoAlmacen(string pArticulo, string pCia, string pAlmacen, int pAnno, int pMes, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if (pArticulo != null && pArticulo.Length > 0)
                {
                    tSql = "SELECT FECHA, NUMERO, TIPO, '0' SalIni, '0' SalFin," +
                        " CASE " +
                        "   WHEN TIPO < 51 then CANT " +
                        "   ELSE 0 " +
                        " END AS Entrada," +
                        " CASE " +
                        "   WHEN TIPO < 51 then 0 " +
                        "   ELSE CANT " +
                        " END AS Salida," +
                        " (VALOR/CANT) Valor " +
                        " FROM ALMACEN.KARDEX" +
                        " WHERE Producto = '" + pArticulo + "'" +
                        " AND Cia = '" + pCia + "'" +
                        " AND Almacen = '" + pAlmacen + "'" +
                        " AND Ano = '" + (pAnno - 2000).ToString("00") + "'" +
                        " AND Periodo = '" + pMes.ToString("00") + "'";
                    if (pClausulaOrderBy.Length > 0)
                    {
                        tSql += " ORDER BY " + pClausulaOrderBy;
                    }
                    else
                    {
                        tSql += " ORDER BY Fecha ";
                    }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public DataTable leerMovimientoAlmacen(string pArticulo, string pCia, string pAlmacen, string pPeriodo, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if (pArticulo != null && pArticulo.Length > 0)
                {
                    tSql = "SELECT FECHA, NUMERO, TIPO, '0' SalIni, '0' SalFin," +
                        " CASE " +
                        "   WHEN TIPO < 51 then CANT " +
                        "   ELSE 0 " +
                        " END AS Entrada," +
                        " CASE " +
                        "   WHEN TIPO < 51 then 0 " +
                        "   ELSE CANT " +
                        " END AS Salida," +
                        " (VALOR/CANT) Valor " +
                        " FROM ALMACEN.KARDEX" +
                        " WHERE Producto = '" + pArticulo + "'" +
                        " AND Cia = '" + pCia + "'" +
                        " AND Almacen = '" + pAlmacen + "'" +
                        " AND Ano = '" + pPeriodo.ToString().Substring(2,2) + "'" +
                        " AND Periodo = '" + pPeriodo.ToString().Substring(4, 2) + "'";
                    if (pClausulaOrderBy.Length > 0)
                    {
                        tSql += " ORDER BY " + pClausulaOrderBy;
                    }
                    else
                    {
                        tSql += " ORDER BY Fecha ";
                    }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public double leerSaldoInicial(string pCia, string pAlmacen, string pArticulo, string pPeriodo)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                tSql = "SELECT ACant as SaldoInicial " +
                    " FROM ALMACEN.Saldos " +
                    " WHERE Cia = '" + pCia + "'" +
                    " AND Almacen = '" + pAlmacen + "'" +
                    " AND Ano = '" + pPeriodo.Substring(2, 2) + "'" +
                    " AND Periodo = '" + pPeriodo.Substring(4, 2) + "'" +
                    " AND Producto = '" + pArticulo + "'";
                string tValor = p.leerValorSQL(tSql);
                //Verificar si trae datos
                if (tValor.Length > 0)
                {
                    return Convert.ToDouble(tValor);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
    }
}
